﻿using System;
using System.Collections.Generic;
using System.Text;
using System.IO;
using MySql.Data;
using MySql.Data.MySqlClient;

namespace cici
{
    class CICIMysql
    {
        private MySqlConnection conn = null;
        private string connString { get; set; }

        private Database database = new Database();

        public CICIMysql()
        {
            this.connString = @"server=" + database.findConfigurationDatabase("hostname") + 
                ";user=" + database.findConfigurationDatabase("username") + ";database=" + 
                database.findConfigurationDatabase("database") + ";port=3306;password=" + 
                database.findConfigurationDatabase("password") + ";";
        }

        protected Boolean testConnection()
        {
            Boolean status = false;
            this.conn = new MySqlConnection(this.connString);
            try
            {
                this.conn.Open();
                status = true;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message.ToString());
            }
            return status;
        }

        protected List<object> getAllTables()
        {
            List<object> resultx = new List<object>();
            try
            {
                string sql = "show tables";
                MySqlCommand cmd = new MySqlCommand(sql, this.conn);
                MySqlDataReader dataReader = cmd.ExecuteReader();

                while (dataReader.Read())
                {
                    resultx.Add(dataReader[0]);
                }
                dataReader.Close();
            }
            catch (Exception ex)
            {
                ex.Message.ToString();
            }

            return resultx;
        }

        protected List<object> getAnyTables(string table)
        {
            List<object> resultx = new List<object>();
            try
            {
                string sql = "show tables like '"+table+"'";
                MySqlCommand cmd = new MySqlCommand(sql, this.conn);
                MySqlDataReader dataReader = cmd.ExecuteReader();

                while (dataReader.Read())
                {
                    resultx.Add(dataReader[0]);
                }
                dataReader.Close();
            }
            catch (Exception ex)
            {
                ex.Message.ToString();
            }

            return resultx;
        }

        protected Dictionary<object, object> getAllColumnsFromTable(string TableName)
        {
            Dictionary<object, object> resultx = new Dictionary<object, object>();
            try
            {
                string sql = "DESCRIBE " + TableName;
                MySqlCommand cmd = new MySqlCommand(sql, this.conn);
                MySqlDataReader dataReader = cmd.ExecuteReader();

                while (dataReader.Read())
                {
                    object temp="input";
                    if ((dataReader[1].ToString().ToLower() == "text") ||
                        (dataReader[1].ToString().ToLower() == "tinytext") ||
                        (dataReader[1].ToString().ToLower() == "mediumtext") ||
                        (dataReader[1].ToString().ToLower() == "longtext"))
                    {
                        temp = "textarea";
                    }
                    resultx.Add(dataReader[0],temp);
                }
                dataReader.Close();
            }
            catch (Exception ex)
            {
                ex.Message.ToString();
            }

            return resultx;
        }
    }
}
